﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Globalization;

namespace ModeloCreator
{
    class Program
    {
        static TextInfo myTI;
        static SqlConnection getConecction()
        {
            SqlConnection connection;            
            connection = new SqlConnection(Properties.Settings.Default.ConnectionString);
            connection.Open();
            return connection;
        }
        static List<string> getAllTables()
        {
            List<string> listTables = new List<string>();
            SqlDataReader dataReader;
            SqlConnection connection = getConecction();
            dataReader = new SqlCommand("SELECT * FROM sys.Tables", connection).ExecuteReader();
            if (dataReader.HasRows)
            {
                while (dataReader.Read())
                {
                    listTables.Add(dataReader.GetString(0));
                }
            }
            connection.Close();
            return listTables;
        }
        static string typeCSharp(string sqlType)
        {
            string value = "*";
            switch (sqlType)
            { 
                case "int":
                    value = "Int32";
                    break;
                case "varchar":
                    value = "string";
                    break;
                case "nvarchar":
                    value = "string";
                    break;
                case "smallint":
                    value = "Int32";
                    break;
                case "bigint":
                    value = "Int32";
                    break;
                case "varbinary":
                    value = "boolean";
                    break;
                case "datetime":
                    value = "DateTime";
                    break;
                case "float":
                    value = "double";
                    break;
                case "text":
                    value = "string";
                    break;
            }
            if (value.Equals("*"))
                throw new Exception("No se controlo la transfomacion de tipo de datos para "+ sqlType);
            return value;
            
        }
        static void createModelClass(string tableName, List<string> listFields)
        {
            myTI = new CultureInfo("en-US", false).TextInfo;
            tableName = myTI.ToTitleCase(tableName).Replace("_", string.Empty);
            tableName = "Base" + tableName;         
            string[] fields = new string[listFields.Count];
            List<string> lines = new List<string>  {
                                @"//Este archivo fue autogenerado por favor no lo modifique", 
                                @"//Autor: David Dominguez", 
                                "using System;",                       
                                "using System.Linq;",
                                "using System.Collections.Generic;",
                                "using System.Text;",
                                "",
                                "namespace dacli.modelos.bases",
                                "{",
                                "    public class "+ tableName +" : Entidad, ICloneable",
                                "    {",
                                @"//Campos",                                
                             };

            foreach (string dataBaseFields in listFields)
            {
                string[] dtFields = dataBaseFields.Split(',');
                myTI = new CultureInfo("en-US", false).TextInfo;
                string fieldCSharp = myTI.ToTitleCase(dtFields[0]).Replace("_", String.Empty);
                fieldCSharp = Char.ToLowerInvariant(fieldCSharp[0]) + fieldCSharp.Substring(1);
                string type = typeCSharp(dtFields[1]);                
                lines.Add("        public " + type + " " + fieldCSharp + "{ get; set; }");
            }
            lines.Add("        public object Clone()");
            lines.Add("        {");
            lines.Add("            return this.MemberwiseClone();");
            lines.Add("        }");
            lines.Add("    }");
            lines.Add("}");
        

            // WriteAllLines creates a file, writes a collection of strings to the file,
            // and then closes the file.
            System.IO.File.WriteAllLines(Properties.Settings.Default.Path + "\\bases\\" + tableName +".cs", lines);
        }

        static List<String> GetFieldsNames(String tableName)
        {
            List<string> listTables = new List<string>();
            SqlDataReader dataReader;
            SqlConnection connection = getConecction();
            string sql = "SELECT COLUMN_NAME,DATA_TYPE FROM information_schema.columns WHERE TABLE_NAME ='"+ tableName+"'";
            dataReader = new SqlCommand(sql, connection).ExecuteReader();
            if (dataReader.HasRows)
            {
                Console.WriteLine(tableName);
                while (dataReader.Read())
                {
                    listTables.Add(dataReader.GetString(0) + "," + dataReader.GetString(1));
                }
            }
            connection.Close();
            return listTables;
        }

        static void Main(string[] args)
        {
            SqlConnection connection = getConecction();
            List<String> tables = getAllTables();
            tables.ForEach(run);
            Console.ReadKey();
        }
        static void run(string tableName)
        {
            Console.WriteLine("Generando Modelo de {0}", tableName);
            List<String> listFields = new List<String>();
            createModelClass(tableName, GetFieldsNames(tableName));
            
        }
    }
}
